import requests
import json
import pymysql

# 定义API请求参数
amap_api_key = "803861c34e31752fb8c5863ba3aa62b4"
url = f"http://restapi.amap.com/v5/place/text?key={amap_api_key}&keywords=北安路&types=050100&region=锦州市"

# 发送请求并解析数据
response = requests.get(url)
data = json.loads(response.text)
pois = data.get('pois', [])  # 获取pois数据，若不存在则默认为空列表

# 准备数据存储
poi_records = []
for poi in pois:
    name = poi.get('name', 'location')  # 获取名称，若不存在则默认为“未知名称”
    location = poi.get('location', '0,0')  # 获取经纬度，若不存在则默认为“0,0”
    poi_records.append((name, location))  # 将每个POI的信息作为一个元组添加到列表中

# 连接数据库并插入数据
try:
    conn = pymysql.connect(host='localhost', user='root', password='151612', database='Python')
    cursor = conn.cursor()

    # 创建表（如果不存在）
    # cursor.execute('''CREATE TABLE IF NOT EXISTS MAP
    #                   (id INT AUTO_INCREMENT PRIMARY KEY,
    #                    name VARCHAR(255),
    #                    location VARCHAR(50))''')
    #
    # # 批量插入数据
    # sql_insert = "INSERT INTO MAP (name, location) VALUES (%s, %s)"
    # cursor.executemany(sql_insert, poi_records)
    # 提交事务
    conn.commit()
    # 查询并打印插入后的结果
    cursor.execute("SELECT * FROM MAP")
    results = cursor.fetchall()
    for row in results:
        print(row)
finally:
    # 关闭游标和连接
    cursor.close()
    conn.close()